import urllib.request
import pymysql
import json
import time


def connection():
    db = pymysql.connect("rm-bp19kpi6407glu0290o.mysql.rds.aliyuncs.com" , "sxrongp2p" , "sxRong@1103" , "onebyone",use_unicode=True, charset="utf8")
    return db


def request_date(pageNum , db):
    url = "http://www.wanandroid.com/article/list/%d/json" % pageNum
    response = urllib.request.urlopen(url)
    json_dict = json.loads(response.read().decode('utf-8'))
    for data in json_dict["data"]["datas"]:
        handler(data , db)

def formatData(timeStamp):
    # print(timeStamp)
    timeStamp /= 1000.0
    timeArray = time.localtime(timeStamp)
    otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
    #print(type(otherStyleTime))
    return otherStyleTime

def handler(data , db):
    cursor = db.cursor()
    #print(data["author"] , data['title'] ,data["link"] , formatData(data["publishTime"]))
    sql = "INSERT INTO article(author , title , link , pulishTime) VALUES( '%s' , '%s' , '%s' ,'%s')" % (data["author"] , data['title'] ,data["link"] , formatData(data["publishTime"]))
    #print(sql)
    try:
       cursor.execute(sql)
       db.commit()
    except Exception as e:
        print(e)
        db.rollback()




if __name__ == '__main__':
    db = connection()
    for num in range(1 , 70):
        request_date(num , db)
    db.close()